package com.example.tally.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.tally.utils.PercentUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:0.2℃
 * @date： 2021-07-14 03:07
 * @Description ：负责管理数据库的类，主要对内容进行增删改查
 */

public class DBManager {
    private static SQLiteDatabase db;
    /**
     * 初始化数据库对象
     */
    public static void initDB(Context context){
        // 获得需要帮助的对象
        DBOpenHelper helper = new DBOpenHelper(context);
        // 获取数据库对象
        db = helper.getWritableDatabase();
    }
    /**
     * 读取数据库中的数据，写入内存集合中
     * kind 表示收入或者支出
     */
    public static List<TypeBean> getTypeList(int kind){
        List<TypeBean> list = new ArrayList<>();
        // 读取typetb表中的数据
        String sql = "select * from typetb where kind = " + kind;
        Cursor cursor = db.rawQuery(sql, null);
        // 循环读取游标内容，存储到对象中
        while(cursor.moveToNext()){
            String typeName = cursor.getString(cursor.getColumnIndex("typeName"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            TypeBean typeBean = new TypeBean(id , typeName, imageId, sImageId, kind);
            list.add(typeBean);
        }
        return list;
    }

    /**
     * 获取某一天的支出或者收入的总金额，kind：支出——>1，收入——>0
     */
    public static float setSumMoneyOneDay(int year , int month , int day , int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + ""});
        // 遍历，因为求出来的是总数，所以应该只有一行
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /**
     * 获取某一月的支出或者收入的总金额，kind：支出——>1，收入——>0
     */
    public static float setSumMoneyOneMonth(int year , int month , int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        // 遍历，因为求出来的是总数，所以应该只有一行
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /**统计某月份支出收入的条数，收入——1，支出——0*/
    public static int getCountItemOneMonth(int year , int month , int kind){
        int total = 0;
        String sql = "select count(money) from accounttb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            int count = cursor.getInt(cursor.getColumnIndex("count(money)"));
            total = count;
        }
        return total;
    }

    /**
     * 获取某一年的支出或者收入的总金额，kind：支出——>1，收入——>0
     */
    public static float setSumMoneyOneYear(int year, int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + ""});
        // 遍历，因为求出来的是总数，所以应该只有一行
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /**
     * 向记账表当中插入一条元素
     */
    public static void insertItemToAccounttb(AccountBean accountBean){
        ContentValues values = new ContentValues();
        values.put("typeName" , accountBean.getTypeName());
        values.put("sImageId" , accountBean.getsImageId());
        values.put("beizhu" , accountBean.getBeizhu());
        values.put("money" , accountBean.getMoney());
        values.put("time" , accountBean.getTime());
        values.put("year" , accountBean.getYear());
        values.put("month" , accountBean.getMonth());
        values.put("day" , accountBean.getDay());
        values.put("kind" , accountBean.getKind());

        db.insert("accounttb" , null , values);
    }
    /**
     * 获取记账表当中的某一天的所有支出或者收入情况
     */
    public static List<AccountBean> getAccountListOneMonthFromAccounttb(int year , int month , int day){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and day=? order by id desc";
        Cursor cursor = db.rawQuery(sql ,
                new String[]{String.valueOf(year), String.valueOf(month), String.valueOf(day)});
        // 遍历符合要求的每一行数据
        while(cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typeName = cursor.getString(cursor.getColumnIndex("typeName"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            AccountBean accountBean = new AccountBean(id, typeName, sImageId, beizhu, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }
    /**
     * 根据传入的id删除accountb表中的一条数据
     */
    public static int deleteItemFromAccounttbById(int id){
        int i = db.delete("accounttb" , "id=?" , new String[]{id+""});
        return i;
    }

    /**
     * 搜索方法：根据备注模糊查询
     */
    public static List<AccountBean> getAccountListByRemarkFromAccounttb(String beizhu){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where beizhu like '%"+ beizhu +"%'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typeName = cursor.getString(cursor.getColumnIndex("typeName"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            String beizhu1 = cursor.getString(cursor.getColumnIndex("beizhu"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typeName, sImageId, beizhu1, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }
    /**获取某一个月份的所有收入或者支出*/
    public static List<AccountBean> getAccountListOneMonthFromAccounttb(int year , int month){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? order by id desc";
        Cursor cursor = db.rawQuery(sql ,
                new String[]{String.valueOf(year), String.valueOf(month)});
        // 遍历符合要求的每一行数据
        while(cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typeName = cursor.getString(cursor.getColumnIndex("typeName"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typeName, sImageId, beizhu, money, time, year, month, day, kind);
            list.add(accountBean);
        }
        return list;
    }
    /**查询记账的表当中有几个年份信息*/
    public static List<Integer> yearListFromAccounttb(){
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(year) from accounttb order by year asc";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            list.add(year);
        }
        return list;
    }
    /**删除accounttb表格当中的所有数据*/
    public static void deleteAllAccounttb(){
        String sql = "delete from accounttb";
        db.execSQL(sql);
    }

    /**查询指定年份和月份的收入或者支出每一种类型总钱数*/
    public static List<CharItemBean> getCharListFromAccounttb(int year , int month , int kind){
        List<CharItemBean> list= new ArrayList<>();
        float oneMonth = setSumMoneyOneMonth(year, month, kind);
        String sql = "select typeName,sImageId,sum(money) as total from accounttb " +
                "where year=? and month=? and kind=? group by typeName order by " +
                "total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()){
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String typeName = cursor.getString(cursor.getColumnIndex("typeName"));
            float total = cursor.getFloat(cursor.getColumnIndex("total"));
            // 计算百分比
            String percent = PercentUtils.percent(total, oneMonth);
            CharItemBean bean = new CharItemBean(sImageId, typeName, percent, total);
            list.add(bean);
        }
        return list;
    }
    /**获取这个月当中某天收入支出的最大值*/
    public static float getMaxMoneyOneDayInMonth(int year , int month , int kind){
        String sql = "select sum(money) from accounttb where year=? and month=? " +
                "and kind=? group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()){
            float maxMoneyOneDay = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return maxMoneyOneDay;
        }
        return 0;
    }

    /**获取指定月份每一天的收入或支出的总钱数*/
    public static List<BarChartItemBean> getSumMoneyOneDayInMonth(int year , int month , int kind){
        List<BarChartItemBean> list = new ArrayList<>();
        String sql = "select day ,sum(money) from accounttb where year=? and month=? " +
                "and kind=? group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()) {
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            float sumMoney = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            BarChartItemBean bean = new BarChartItemBean(year, month, day, sumMoney);
            list.add(bean);
        }
        return list;
    }
}

